import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import model.Device;
import model.Group;
import model.User;


public class DAO {
	private Connection conn;
	
	public DAO(){
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection("jdbc:mysql://localhost/c2dm", "root", "hajfot59");
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
	
	public void close(){
		if(conn != null)
			try {
				conn.close();
			} catch (SQLException e) {}
	}
	
	public Device getDeviceById(int id){
		Device d = null;
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from device where id = " + id);
			if(rs.next()){
				d = new Device();
				d.setId(rs.getInt("id"));
				d.setDeviceId(rs.getString("device_id"));
				d.setDeviceKey(rs.getString("device_key"));
			}
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return d;
	}
	
	public Device getDeviceByDeviceId(String deviceId){
		Device d = null;
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from device where device_id = \""  + deviceId + "\";");
			if(rs.next()){
				d = new Device();
				d.setId(rs.getInt("id"));
				d.setDeviceId(rs.getString("device_id"));
				d.setDeviceKey(rs.getString("device_key"));
			}
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return d;
	}
	
	public int addOrUpdateDevice(String deviceId, String deviceKey){
		int id = -1;
		try {
			Device d = getDeviceByDeviceId(deviceId);//Check if device exist
			Statement stmt = conn.createStatement();
			if(d == null){//new Device
				stmt.executeUpdate("insert into device (device_id, device_key) values ('" + deviceId + "','" + deviceKey + "');", Statement.RETURN_GENERATED_KEYS);
				ResultSet rs = stmt.getGeneratedKeys();
				if(rs.next()) {
					id = rs.getInt(1);
				}
			}else{//Old Device
				id = d.getId();
				stmt.executeUpdate("update device set device_key = '" + deviceKey + "' where id = " + id);
			}
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return id;
	}
	
	public User getUserById(int id){
		return getUser("select * from user where id = " + id);
	}
	
	public User getUserByName(String name){
		return getUser("select * from user where name = '" + name + "'");
	}
	
	private User getUser(String query){
		User u = null;
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs.next()){
				u = new User();
				u.setId(rs.getInt("id"));
				u.setName(rs.getString("name"));
				u.setWin(rs.getInt("win"));
				u.setLost(rs.getInt("lost"));
				u.setDeviceId(rs.getInt("device_id"));
			}
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return u;
	}
	
	public int addUser(String name, int win, int lost, int device_id){
		int id = -1;
		try {
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("insert into user (name, win, lost, device_id) values ('" + name + "'," + win + "," + lost + "," + device_id + ");", Statement.RETURN_GENERATED_KEYS);
			ResultSet rs = stmt.getGeneratedKeys();
			if(rs.next()) {
				id = rs.getInt(1);
			}
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return id;
	}
	
	public boolean updateUser(User u){
		if(u.getId() == -1 || u.getDeviceId() == -1)
			return false;
		try {
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("update user set win = " + u.getWin() + ", lost = " + u.getLost() + ", device_id =" + u.getDeviceId() + " where id = " + u.getId());
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	public boolean addUserToGroup(int userId, int groupId){
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from userGroup where userId =" + userId + " and groupId = " + groupId);
			if(!rs.next())
				stmt.executeUpdate("insert into userGroup (userId, groupId) values (" + userId + "," + groupId + ");");
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	public boolean removeUserFromGroup(int userId, int groupId){
		try {
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("delete from userGroup where userId =" + userId + " and groupId =" + groupId);
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	public int addGroup(String groupName){
		int id = -1;
		try {
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("insert into grouptable (name) values ('" + groupName + "')", Statement.RETURN_GENERATED_KEYS);
			ResultSet rs = stmt.getGeneratedKeys();
			if(rs.next()) {
				id = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return id;
	}
	
	public int getGroupIdFromName(String groupName){
		int id = -1;
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from grouptable where name = \"" + groupName + "\";");
			if(rs.next()){
				id = rs.getInt("id");
			}
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return id;
	}
	
	public List<User> getUsersInGroup(String groupName){
		int id = -1;
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from grouptable where name ='" + groupName + "'");
			if(rs.next()){
				id = rs.getInt("id");
			}
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return getUsersInGroup(id);
	}
	
	public List<User> getUsersInGroup(int groupId){
		ArrayList<User> uList = new ArrayList<User>();
		if(groupId < 0)
			return uList;
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select u.id, u.name, u.win, u.lost, u.device_id from user u, userGroup ug where u.id = ug.userId and groupId =" + groupId);
			while(rs.next()){
				User u = new User();
				u.setId(rs.getInt("id"));
				u.setName(rs.getString("name"));
				u.setWin(rs.getInt("win"));
				u.setLost(rs.getInt("lost"));
				u.setDeviceId(rs.getInt("device_id"));
				uList.add(u);
			}
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return uList;
	}
	
	public List<Group> getGroupsThatUserIsPartIn(User u){
		ArrayList<Group> groups = new ArrayList<Group>();
		try {
			Statement stmt = conn.createStatement();
//											"select * from T_Order o,T_ORDERITEMS i where o.order_id = i.order_id and o.status = 0;"
			ResultSet rs = stmt.executeQuery("select g.id, g.name from usergroup ug, grouptable g where ug.groupId = g.id and ug.userId = " + u.getId());
			while(rs.next()){
				Group g = new Group();
				g.setId(rs.getInt("id"));
				g.setName(rs.getString("name"));
				groups.add(g);
			}
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return groups;
	}

	public List<User> getTopNUsers(int n){
		ArrayList<User> uList = new ArrayList<User>();
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from user order by win");
			int i = 0;
			while(rs.next() && i < n){
				User u = new User();
				u.setId(rs.getInt("id"));
				u.setName(rs.getString("name"));
				u.setWin(rs.getInt("win"));
				u.setLost(rs.getInt("lost"));
				u.setDeviceId(rs.getInt("device_id"));
				uList.add(u);
				i++;
			}
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return uList;
	}
	
	public List<User> getTopNUsersFromGroup(int n, int groupId){
		ArrayList<User> uList = new ArrayList<User>();
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select u.id, u.name, u.win, u.lost, u.device_id from user u, userGroup ug where u.id = ug.userId and ug.groupId = " + groupId + " order by u.win");
			int i = 0;
			while(rs.next() && i < n){
				User u = new User();
				u.setId(rs.getInt("id"));
				u.setName(rs.getString("name"));
				u.setWin(rs.getInt("win"));
				u.setLost(rs.getInt("lost"));
				u.setDeviceId(rs.getInt("device_id"));
				uList.add(u);
				i++;
			}
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return uList;
	}
}
